import sys
import os
import logging
import re
import json
from typing import Dict, List, Any, Optional, Tuple

# 添加项目根路径到 sys.path
sys.path.append(os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', '..'))

# 导入 MySQLUtil
from shared.utils.MySQLUtil import MySQLUtil

# 设置日志
logger = logging.getLogger(__name__)

def get_各学历及总体_就业职业分布(
    project_id: int,
    questionnaire_ids: List[int],
    product_code: Optional[str] = None,
    project_code: Optional[str] = None,
    region_code: Optional[str] = None,
    education: Optional[str] = None
) -> Dict[str, Any]:
    """
    各学历及总体，就业职业分布 - 指标计算函数
    
    ## 指标说明
    计算不同学历层次及总体就业学生的职业分布情况，统计各职业门类就业人数占比。
    数据来源为学生客观表，过滤条件为：毕业去向不为待就业和暂不就业，且职业门类字段不为空。
    计算结果包含各学历层次(本科、专科、硕士、博士)及总体的职业分布占比。
    
    ## Args
        project_id (int): 项目ID，用于查询项目配置信息
        questionnaire_ids (List[int]): 问卷ID集合，用于确定数据范围
        product_code (Optional[str]): 产品编码，用于路由到特定计算逻辑
        project_code (Optional[str]): 项目编码，用于路由到特定计算逻辑
        region_code (Optional[str]): 区域编码，用于路由到特定计算逻辑
        education (Optional[str]): 学历层次筛选条件，可选值为[本科毕业生，专科毕业生，硕士研究生，博士研究生]
        
    ## 示例
    ### 输入
    ```json
    {
        "project_id": 5895,
        "questionnaire_ids": [11158, 11159]
    }
    ```
    
    ### 输出
    ```json
    {
        "success": true,
        "message": "ok",
        "code": 0,
        "result": [
            {
                "group": "本科毕业生",
                "data": [
                    {"职业门类": "工程技术人员", "占比": 0.35},
                    {"职业门类": "教学人员", "占比": 0.25}
                ]
            },
            {
                "group": "总体",
                "data": [
                    {"职业门类": "工程技术人员", "占比": 0.30},
                    {"职业门类": "教学人员", "占比": 0.20}
                ]
            }
        ]
    }
    ```
    """
    logger.info(f"开始计算指标: 各学历及总体，就业职业分布, 项目ID: {project_id}")
    
    try:
        db = MySQLUtil()  

        # 1. 查询项目信息
        project_sql = """
        SELECT client_code, item_year FROM client_item WHERE id = %s
        """
        project_info = db.fetchone(project_sql, (project_id,))
        if not project_info:
            raise ValueError(f"未找到项目ID={project_id}的配置信息")

        client_code = project_info['client_code']
        item_year = project_info['item_year']
        
        logger.info(f"项目配置: client_code={client_code}, item_year={item_year}")

        # 2. 计算 shard_tb_key
        shard_tb_key = re.sub(r'^[A-Za-z]*0*', '', client_code)
        logger.info(f"计算得到 shard_tb_key: {shard_tb_key}")

        # 3. 处理学历参数（空字符串转为None）
        if education == '':
            education = None
        
        # 4. 构建结果容器
        result = []
        education_list = ['本科毕业生', '专科毕业生', '硕士研究生', '博士研究生']
        
        # 5. 如果指定了学历，只计算该学历；否则计算所有学历
        if education:
            if education not in education_list:
                raise ValueError(f"学历参数必须是以下值之一: {education_list}")
            education_list = [education]
        
        # 6. 计算各学历层次的职业分布
        for edu in education_list:
            # 计算分母(该学历就业总人数)
            denominator_sql = f"""
            SELECT count(*) AS total 
            FROM dim_client_target_baseinfo_student_calc_{item_year} 
            WHERE shard_tb_key = %s 
              AND grad_dest_merge NOT IN ('待就业', '暂不就业')
              AND occ_class_obj != ''
              AND education = %s
            """
            denominator = db.fetchone(denominator_sql, (shard_tb_key, edu))
            if not denominator or denominator['total'] == 0:
                continue
                
            # 计算各职业门类人数及占比
            sql = f"""
            SELECT 
                occ_class_obj, 
                count(*) as count, 
                count(*)/%s AS ratio_val
            FROM dim_client_target_baseinfo_student_calc_{item_year}
            WHERE shard_tb_key = %s
              AND grad_dest_merge NOT IN ('待就业', '暂不就业')
              AND occ_class_obj != ''
              AND education = %s
            GROUP BY occ_class_obj
            ORDER BY ratio_val DESC
            LIMIT 10
            """
            rows = db.fetchall(sql, (float(denominator['total']), shard_tb_key, edu))
            
            if rows:
                edu_data = {
                    "group": edu,
                    "data": [{"职业门类": row['occ_class_obj'], "占比": float(row['ratio_val'])} for row in rows]
                }
                result.append(edu_data)

        # 5. 计算总体职业分布
        total_denominator_sql = f"""
        SELECT count(*) AS total 
        FROM dim_client_target_baseinfo_student_calc_{item_year} 
        WHERE shard_tb_key = %s 
          AND grad_dest_merge NOT IN ('待就业', '暂不就业')
          AND occ_class_obj != ''
        """
        total_denominator = db.fetchone(total_denominator_sql, (shard_tb_key,))
        
        if total_denominator and total_denominator['total'] > 0:
            total_sql = f"""
            SELECT 
                occ_class_obj, 
                count(*) as count, 
                count(*)/%s AS ratio_val
            FROM dim_client_target_baseinfo_student_calc_{item_year}
            WHERE shard_tb_key = %s
              AND grad_dest_merge NOT IN ('待就业', '暂不就业')
              AND occ_class_obj != ''
            GROUP BY occ_class_obj
            ORDER BY ratio_val DESC
            LIMIT 10
            """
            total_rows = db.fetchall(total_sql, (float(total_denominator['total']), shard_tb_key))
            
            if total_rows:
                total_data = {
                    "group": "总体",
                    "data": [{"职业门类": row['occ_class_obj'], "占比": float(row['ratio_val'])} for row in total_rows]
                }
                result.append(total_data)

        logger.info(f"指标 '各学历及总体，就业职业分布' 计算成功")
        return {
            "success": True,
            "message": "ok",
            "code": 0,
            "result": result
        }

    except Exception as e:
        logger.error(f"计算指标 '各学历及总体，就业职业分布' 时发生错误: {str(e)}", exc_info=True)
        return {
            "success": False,
            "message": f"数据获取失败: 各学历及总体，就业职业分布",
            "code": 500,
            "error": str(e)
        }